﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Scheduling.Models.Interfaces;
using Scheduling.Models.Helpers;
using Scheduling.Models.Repos.Database.Objects;

namespace Scheduling.Models.Repos.Database
{
    public class DatabaseRoomLoader : IRoomLoader
    {
        public List<Room> GetRooms()
        {
            var db = DBContext.Create();
            var q = from r in db.Rooms
                    select new LinqRoom
                    {
                        Room = r,
                        Properties = from rp in db.RoomProperties
                                     join p in db.Properties on rp.PropertyID equals p.ID
                                     where (rp.RoomID == r.ID)
                                     select new RoomProperty
                                     {
                                         Name = p.Name,
                                         Value = rp.Value,
                                     }
                    };
            return MapRooms(q);
        }

        public List<Room> SearchRoomsByPatten(string Pattern)
        {
            var db = DBContext.Create();
            var q = from c in db.Rooms
                    where c.ID.Contains(Pattern) || c.Name.Contains(Pattern)
                    select new { c };
            return LinqHelper.GenericMap<Room>(q, "c");
        }

        private List<Room> MapRooms(IQueryable<LinqRoom> lqRoom)
        {
            var result = new List<Room>();
            foreach (var room in lqRoom)
                result.Add(MapRoom(room));

            return result;
        }

        private Room MapRoom(LinqRoom lqRoom)
        {
            var properties = new List<RoomProperty>();
            foreach (var prop in lqRoom.Properties) 
                properties.Add(new RoomProperty() { Name = prop.Name, Value = prop.Value });

            var room = new Room()
            {
                ID = lqRoom.Room.ID,
                Name = lqRoom.Room.Name,
                Description = lqRoom.Room.Description,
                Capacity = lqRoom.Room.Capacity,
                Properties = properties
            };
            return room;
        }


        public bool RoomExists(string RoomID)
        {
            var db = DBContext.Create();
            return (from r in db.Rooms where r.ID == RoomID select r).Count() > 0;
        }

        public Room GetRoom(string RoomID)
        {
            var db = DBContext.Create();
            var q = from r in db.Rooms where r.ID == RoomID select new LinqRoom
                    {
                        Room = r,
                        Properties = from rp in db.RoomProperties
                                     join p in db.Properties on rp.PropertyID equals p.ID
                                     where (rp.RoomID == r.ID)
                                     select new RoomProperty
                                     {
                                         Name = p.Name,
                                         Value = rp.Value,
                                     }
                    };
            if (q.Count() > 0)
                return MapRoom(q.First());
            else
                return null;
        }
        public void DeleteRoom(string RoomID)
        {
            var db = DBContext.Create();
            var q = from r in db.Rooms select r;
            if (q.Count() > 0)
                db.ExecuteCommand(string.Format("DELETE FROM Rooms WHERE ID = '{0}'", RoomID));
            db.ExecuteCommand(string.Format("DELETE FROM RoomProperties WHERE RoomID = '{0}'", RoomID));
        }

        public void InsertRoom(Room room)
        {
            var db = DBContext.Create();
            var rpL = IoC.Resolve<IRoomPropertyLoader>();
            db.ExecuteCommand(
                string.Format("INSERT INTO Rooms(ID, Name, Capacity, Description) VALUES('{0}', '{1}', {2}, '{3}')",
                room.ID,room.Name,room.Capacity,room.Description));
            List<RoomProperty> create = new List<RoomProperty>();
            foreach (RoomProperty rp in room.Properties)
            {
                if (!rpL.RoomPropertyExists(rp.Name))
                    create.Add(rp);

            }
            foreach (RoomProperty rp in create)
            {
                db.ExecuteCommand(string.Format("INSERT INTO Properties(Name) VALUES('{0}')", rp.Name));
            }

            foreach(RoomProperty rp in room.Properties)
            {
                db.ExecuteCommand(
                string.Format("INSERT INTO RoomProperties(RoomID, PropertyID, Value) VALUES('{0}', {1}, {2})",
                room.ID, rpL.GetPropertyID(rp.Name), rp.Value));
            }

        }

        public void EditRoom(Room room)
        {
            var db = DBContext.Create();
            var rpLoad = IoC.Resolve<IRoomPropertyLoader>();
            db.ExecuteCommand(
                string.Format("UPDATE Rooms SET Name = '{1}', Capacity = '{2}', Description = '{3}' WHERE ID = '{0}'",
                room.ID, room.Name, room.Capacity, room.Description));
            var rpL = (from p in db.RoomProperties where p.RoomID == room.ID select p);
                db.ExecuteCommand(
                    string.Format("DELETE FROM RoomProperties WHERE RoomID = '{0}'", room.ID));
                List<RoomProperty> create = new List<RoomProperty>();
                foreach (RoomProperty rp in room.Properties)
                {
                    if (!rpLoad.RoomPropertyExists(rp.Name))
                        create.Add(rp);

                }
                foreach (RoomProperty rp in create)
                {
                    db.ExecuteCommand(string.Format("INSERT INTO Properties(Name) VALUES('{0}')", rp.Name));
                }

                foreach (RoomProperty rp in room.Properties)
                {
                    db.ExecuteCommand(
                    string.Format("INSERT INTO RoomProperties(RoomID, PropertyID, Value) VALUES('{0}', {1}, {2})",
                    room.ID, rpLoad.GetPropertyID(rp.Name), rp.Value));
                }

            
            
        }

        public void Clear()
        {
            throw new NotImplementedException();
        }
    }

}